package org.example.musicproject.dao.impl;

import org.example.musicproject.dao.SongDao;
import org.example.musicproject.entity.Song;
import org.example.musicproject.util.DButil;

import java.sql.*;
import java.util.Collections;
import java.util.List;

/**
 * 创建人:黄庭萍
 * 创建时间: 2024/12/3 19:10
 * 创建类型: testDaoImpl
 */
public class SongDaoImpl implements SongDao {
    @Override
    public List<Song> findAll() {
        String sql = "SELECT id AS soid, name AS soname, introduction, createDate,updateDate, pic, lyric, url, mvurl, num, isVip FROM song";
        System.out.println("=====查询所有歌曲信息============");
        System.out.println("sql="+sql);
        System.out.println("================================");
        return DButil.list(sql, Song.class);
    }

    @Override
    public Song byId(int id) {
        String sql = "SELECT id AS soid, name AS soname, introduction, createDate,updateDate, pic, lyric, url, mvurl, num, isVip FROM song where id = ?";
        System.out.println("=====根据id查询歌曲信息============");
        System.out.println("sql="+sql);
        System.out.println("id="+id);
        System.out.println("================================");
        return DButil.one(sql, Song.class, id);
    }

    @Override
    public long add(Connection conn, Song song) {
        String sql = "INSERT INTO song (name, introduction, createDate, pic, lyric, url, mvurl, num, isVip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        long id = 0l;
        try (PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, song.getSoname());
            pstmt.setString(2, song.getIntroduction());
            pstmt.setDate(3, new java.sql.Date(song.getCreateDate().getTime()));
            pstmt.setString(4, song.getPic());
            pstmt.setString(5, song.getLyric());
            pstmt.setString(6, song.getUrl());
            pstmt.setString(7, song.getMvurl());
            pstmt.setInt(8, song.getNum());
            pstmt.setBoolean(9, song.getIsVip());
            int rowsAffected = pstmt.executeUpdate();
            if (rowsAffected == 0) {
                throw  new SQLException("歌曲添加失败!");
            }
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getLong(1);
                System.out.println("生成的歌曲ID编号:"+id);
            }else {
                throw  new SQLException("歌曲添加失败!没有生成歌曲ID编号");
            }
        }catch (SQLException e) {
            throw new RuntimeException("歌曲添加失败!原因:"+e.getMessage());
        }
        System.out.println("=====查询所有歌曲信息============");
        System.out.println("sql="+sql);
        System.out.println("conn="+conn);
        System.out.println("song="+song);
        System.out.println("================================");
        return id;
    }

    @Override
    public int update(Song song) {
        String sql="update song set name=?,introduction=?,updateDate=?,pic=?,lyric=?,url=?,mvurl=?,isVip=? where id=?";
        System.out.println("=====添加歌曲信息============");
        System.out.println("sql="+sql);
        System.out.println("song="+song.getSoid());
        System.out.println("================================");
        return DButil.update(sql, song.getSoname(),song.getIntroduction(),song.getUpdateDate(),song.getPic(),song.getLyric()
        ,song.getUrl(),song.getMvurl(),song.getIsVip(),song.getSoid());
    }

    @Override
    public int isVips(int id, boolean isVip) {
        String sql="update song set isVip=? where id=?";
        System.out.println("=====修改歌曲信息中的vip状态(0不收费,1收费)============");
        System.out.println("sql="+sql);
        System.out.println("id="+id);
        System.out.println("isVip="+isVip);
        System.out.println("================================");
        return DButil.update(sql,isVip,id);
    }

    @Override
    public List<Song> bykey(String key) {
        // 使用别名确保列名与实体类字段名一致，并在 SQL 中直接添加通配符
        String sql = "SELECT id AS soid, name AS soname, introduction, createDate, updateDate, pic, lyric, url, mvurl, num, isVip FROM song WHERE name LIKE ?";
        System.out.println("=====根据关键字查询所有歌曲信息============");
        System.out.println("sql=" + sql);
        System.out.println("key=" + "%" + key + "%");
        System.out.println("================================");

        // 执行查询并获取结果列表
        List<Song> list = DButil.list(sql, Song.class, "%" + key + "%");

        // 如果没有找到任何记录，返回空列表而不是 null
        if (list == null || list.isEmpty()) {
            return Collections.emptyList();
        }

        return list;
    }

    @Override
    public List<Song> number(int num) {
        String sql = "SELECT s.id AS soid, s.name AS soname, s.num \n" +
                "FROM song s\n" +
                "ORDER BY s.num DESC\n" +
                "LIMIT ?";
        System.out.println("=====查找最受欢迎的歌曲（基于播放次数）============");
        System.out.println("sql=" + sql);
        System.out.println("num=" + num);
        System.out.println("================================");

        // 使用 DButil.query 或 DButil.list 方法来执行查询，并将结果映射到 Song 实体类
        List<Song> number = DButil.list(sql, Song.class, num);

        return number != null ? number : Collections.emptyList();
    }

}
